package bbs2.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import bbs2.model.Comment;

public class CommentDAO {

    private static Comment makeComment(ResultSet rs) throws Exception {
        Comment comment = new Comment();
        comment.setId(rs.getInt("ID"));
        comment.setTargetType(rs.getInt("targetType"));
        comment.setTargetId(rs.getInt("targetId"));
        comment.setUserId(rs.getInt("userId"));
        comment.setName(rs.getString("name"));
        comment.setWriteTime(rs.getTimestamp("writeTime"));
        comment.setBody(rs.getString("body"));
        return comment;
    }
    
    public static ArrayList<Comment> select(int targetType, int targetId) throws Exception {
        ArrayList<Comment> list = new ArrayList<Comment>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DB.getConnection();
            String sql = "EXEC commentSelectAll ?, ?"; 
            statement = connection.prepareStatement(sql);
            statement.setInt(1, targetType);
            statement.setInt(2, targetId);
            resultSet = statement.executeQuery();
            while (resultSet.next()) 
                list.add(makeComment(resultSet));
        } finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }
        return list;
    }    
    
    public static void insert(int userId, int targetType, int targetId, String body) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "INSERT [Comment] ([targetType], [targetId], [userID], [writeTime], [body]) VALUES (?, ?, ?, GETDATE(), ?)"; 
            statement = connection.prepareStatement(sql);
            statement.setInt(1, targetType);
            statement.setInt(2, targetId);
            statement.setInt(3, userId);
            statement.setString(4, body);
            statement.executeUpdate();
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }        
    }

    public static void update(int id, String body) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "UPDATE [Comment] SET [body] = ?, [writeTime] = GETDATE() WHERE [id] = ?"; 
            statement = connection.prepareStatement(sql);
            statement.setString(1, body);
            statement.setInt(2, id);
            statement.executeUpdate();
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }        
    }

    public static void delete(int id) throws Exception {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DB.getConnection();
            String sql = "DELETE [Comment] WHERE [id] = ?"; 
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            statement.executeUpdate();
        } finally {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        }        
    }

}
